package ar.com.larctest.providers;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

import ar.com.larctest.dbHelper.SQLLARCTESTHelper;
import ar.com.larctest.entities.Horses;
import ar.com.larctest.entities.HorsesRacing;
import ar.com.larctest.utils.AppGlobalVariables;
import ar.com.larctest.utils.Constants;

public class HorsesProvider {
    private static final String TAG = "HorsesProvider";

	private static final String DB_COLUMN_0_NAME = "idHorse";
	private static final String DB_COLUMN_1_NAME = "name";
    private static final String DB_COLUMN_2_NAME = "yearsOld";
    private static final String DB_COLUMN_3_NAME = "hair";
    private static final String DB_COLUMN_4_NAME = "temperament";
    private static final String DB_COLUMN_5_NAME = "state";
    private static final String DB_COLUMN_6_NAME = "idUser";
    private static final String DB_COLUMN_7_NAME = "idHorseWeb";

    private static final String DB_COLUMN_REL_HORSES_RACING = "idHorse";

	private SQLiteDatabase database;
	private SQLLARCTESTHelper dbHelper;
    private Context context;
	private String[] allColumns = {DB_COLUMN_0_NAME, DB_COLUMN_1_NAME, DB_COLUMN_2_NAME, DB_COLUMN_3_NAME, DB_COLUMN_4_NAME, DB_COLUMN_5_NAME, DB_COLUMN_6_NAME, DB_COLUMN_7_NAME};

	public HorsesProvider(Context context) {
		dbHelper = SQLLARCTESTHelper.getHelper(context);
		database = dbHelper.getWritableDatabase();
        this.context = context;
	}

    public long insertHorse(Horses horses)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DB_COLUMN_0_NAME, horses.getIdHorse());
        contentValues.put(DB_COLUMN_1_NAME, horses.getName());
        contentValues.put(DB_COLUMN_2_NAME, horses.getYearsOld());
        contentValues.put(DB_COLUMN_3_NAME, horses.getHair());
        contentValues.put(DB_COLUMN_4_NAME, horses.getTemperament());
        contentValues.put(DB_COLUMN_5_NAME, horses.getState());
        contentValues.put(DB_COLUMN_6_NAME, horses.getIdUser());
        contentValues.put(DB_COLUMN_7_NAME, horses.getIdHorseWeb());

        long recordId = database.insert(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, null, contentValues);

        return recordId;
    }
		
	public long insertOrUpdateHorse(Horses horses)
	{
		ContentValues contentValues = new ContentValues();
		contentValues.put(DB_COLUMN_0_NAME, horses.getIdHorse());
		contentValues.put(DB_COLUMN_1_NAME, horses.getName());
        contentValues.put(DB_COLUMN_2_NAME, horses.getYearsOld());
        contentValues.put(DB_COLUMN_3_NAME, horses.getHair());
        contentValues.put(DB_COLUMN_4_NAME, horses.getTemperament());
        contentValues.put(DB_COLUMN_5_NAME, horses.getState());
        contentValues.put(DB_COLUMN_6_NAME, horses.getIdUser());
        contentValues.put(DB_COLUMN_7_NAME, horses.getIdHorseWeb());

        Cursor cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, DB_COLUMN_0_NAME + " = " + horses.getIdHorse(), null, null, null, null);

        long recordId;
        if(cursor.getCount()>0)
            recordId = database.update(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, contentValues, DB_COLUMN_0_NAME + " = " + horses.getIdHorse(), null);
        else
            recordId = database.insert(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, null, contentValues);

        //Add Horse Racings
        HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
        horsesRacingProvider.removeRacingsHorse((int)recordId);
        for (HorsesRacing horsesRacing: horses.getRacings()) {
            horsesRacing.setIdHorse((int)recordId);
            horsesRacingProvider.insertHorsesRacings(horsesRacing);
        }

        return recordId;
	}

    public boolean updateHorsesIdWeb(Integer idHorse, Integer idWeb)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DB_COLUMN_5_NAME, Constants.UPDATE_RECORD);
        contentValues.put(DB_COLUMN_7_NAME, idWeb);

        long recordId = database.update(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, contentValues, DB_COLUMN_0_NAME + " = " + idHorse, null);

        return recordId >0;
    }
	
	public boolean removeHorse(Integer idHorse)
	{
        database.delete(SQLLARCTESTHelper.DB_HORSES_RACING_TABLE_NAME, DB_COLUMN_REL_HORSES_RACING + " = " + idHorse, null);
		int result = database.delete(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, DB_COLUMN_0_NAME + " = " + idHorse, null);

        return result > 0;
	}
	
	public boolean removeHorses()
	{
        database.delete(SQLLARCTESTHelper.DB_HORSES_RACING_TABLE_NAME, null, null);
		int result = database.delete(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, null, null);

        return result > 0;
	}
		
	public long updateHorse(Horses horses)
	{
		ContentValues contentValues = new ContentValues();
        contentValues.put(DB_COLUMN_0_NAME, horses.getIdHorse());
        contentValues.put(DB_COLUMN_1_NAME, horses.getName());
        contentValues.put(DB_COLUMN_2_NAME, horses.getYearsOld());
        contentValues.put(DB_COLUMN_3_NAME, horses.getHair());
        contentValues.put(DB_COLUMN_4_NAME, horses.getTemperament());
        contentValues.put(DB_COLUMN_5_NAME, horses.getState());
        contentValues.put(DB_COLUMN_6_NAME, horses.getIdUser());
        contentValues.put(DB_COLUMN_7_NAME, horses.getIdHorseWeb());

        long recordId = database.update(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, contentValues, DB_COLUMN_0_NAME + " = " + horses.getIdHorse(), null);

        //Add Horse Racings
        HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
        for (HorsesRacing horsesRacing: horses.getRacings()) {
            horsesRacingProvider.updateHorsesRacings(horsesRacing);
        }

        return recordId;
	}
	
	public Horses getHorse(Integer idHorse)
	{
		Cursor cursor = null;
		Horses horse  = null;
		
		try
		{
            cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, DB_COLUMN_0_NAME + " = " + idHorse, null, null, null, null);
			cursor.moveToFirst();
			
			horse = new Horses();
            horse.setIdHorse(cursor.getInt(0)) ;
            horse.setName(cursor.getString(1));
            horse.setYearsOld(cursor.getInt(2));
            horse.setHair(cursor.getString(3));
            horse.setTemperament(cursor.getString(4));
            horse.setState(cursor.getString(5));
            horse.setIdUser(cursor.getInt(6));
            horse.setIdHorseWeb(cursor.getInt(7));
            horse.setSelected(false);

            HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
            horse.setRacings(horsesRacingProvider.getHorsesRacing(cursor.getInt(0)));

            return horse;
		}
		catch (Exception e) {
			Log.e(TAG, e.getMessage());
			return horse;
		}
		finally {
            if (cursor != null) {
                cursor.close();
            }
        }
	}
	
	public List<Horses> getHorses() {
		
		Cursor cursor = null;
		List<Horses> lstHorses = new ArrayList<Horses>();
		
		try
		{
			cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, DB_COLUMN_6_NAME + " = " + AppGlobalVariables.getInstance().getIdUser(), null, null, null, null);
			cursor.moveToFirst();
			
			while (!cursor.isAfterLast()) {
                Horses horse = new Horses();
                horse = new Horses();
                horse.setIdHorse(cursor.getInt(0)) ;
                horse.setName(cursor.getString(1));
                horse.setYearsOld(cursor.getInt(2));
                horse.setHair(cursor.getString(3));
                horse.setTemperament(cursor.getString(4));
                horse.setState(cursor.getString(5));
                horse.setIdUser(cursor.getInt(6));
                horse.setIdHorseWeb(cursor.getInt(7));
                horse.setSelected(false);

                HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
                horse.setRacings(horsesRacingProvider.getHorsesRacing(cursor.getInt(0)));

                lstHorses.add(horse);
				cursor.moveToNext();
			}
			
			return lstHorses;
		}
		catch(Exception e) {
			Log.e(TAG, e.getMessage());
			return lstHorses;
		}
		finally {
            if (cursor != null) {
                cursor.close();
            }
        }
	}

    public List<Horses> getHorses(String strName) {

        Cursor cursor = null;
        List<Horses> lstHorses = new ArrayList<Horses>();

        try
        {
            //Limit to Login User
            String sWhere;
            if(strName.equals(""))
                cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, DB_COLUMN_6_NAME + " = " + AppGlobalVariables.getInstance().getIdUser(), null, null, null, null);
            else
                cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, DB_COLUMN_1_NAME + " LIKE '%" + strName + "%' AND " + DB_COLUMN_6_NAME + " = " + AppGlobalVariables.getInstance().getIdUser(), null, null, null, null);
            cursor.moveToFirst();

            while (!cursor.isAfterLast()) {
                Horses horse = new Horses();
                horse = new Horses();
                horse.setIdHorse(cursor.getInt(0)) ;
                horse.setName(cursor.getString(1));
                horse.setYearsOld(cursor.getInt(2));
                horse.setHair(cursor.getString(3));
                horse.setTemperament(cursor.getString(4));
                horse.setState(cursor.getString(5));
                horse.setIdUser(cursor.getInt(6));
                horse.setIdHorseWeb(cursor.getInt(7));
                horse.setSelected(false);

                HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
                horse.setRacings(horsesRacingProvider.getHorsesRacing(cursor.getInt(0)));

                lstHorses.add(horse);
                cursor.moveToNext();
            }

            return lstHorses;
        }
        catch(Exception e) {
            Log.e(TAG, e.getMessage());
            return lstHorses;
        }
        finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

    public List<Horses> getHorsesWidthOutMeasurement() {
        Cursor cursor = null;
        List<Horses> lstHorses = new ArrayList<Horses>();

        try
        {
            String sSQLWhere = DB_COLUMN_0_NAME + " NOT IN (SELECT " + DB_COLUMN_0_NAME + " FROM " + SQLLARCTESTHelper.DB_MEASUREMENT_TEMP_ITEM_TABLE_NAME + ") AND " +DB_COLUMN_6_NAME + " = " + AppGlobalVariables.getInstance().getIdUser();

            cursor = database.query(SQLLARCTESTHelper.DB_HORSES_TABLE_NAME, allColumns, sSQLWhere, null, null, null, null);
            cursor.moveToFirst();

            while (!cursor.isAfterLast()) {
                Horses horse = new Horses();
                horse = new Horses();
                horse.setIdHorse(cursor.getInt(0)) ;
                horse.setName(cursor.getString(1));
                horse.setYearsOld(cursor.getInt(2));
                horse.setHair(cursor.getString(3));
                horse.setTemperament(cursor.getString(4));
                horse.setState(cursor.getString(5));
                horse.setIdUser(cursor.getInt(6));
                horse.setIdHorseWeb(cursor.getInt(7));
                horse.setSelected(false);

                HorsesRacingProvider horsesRacingProvider = new HorsesRacingProvider(context);
                horse.setRacings(horsesRacingProvider.getHorsesRacing(cursor.getInt(0)));

                lstHorses.add(horse);
                cursor.moveToNext();
            }

            return lstHorses;
        }
        catch(Exception e) {
            Log.e(TAG, e.getMessage());
            return lstHorses;
        }
        finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }
}
